
Create VIEW [dbo].[VWaitingListReport]
AS

select wfSteps.DisplayName,count(wfSteps.DisplayName) as NumberOfLoans,
		sum(CustomerLoans.LoanAmount) as TotalAmount,
		CustomerLoans.CommitteeID,Projects.ProjectID
		from CustomerLoans 
				inner join ProjectStages on ProjectStages.ProjectStageID = CustomerLoans.ProjectStageID
				inner join Projects on ProjectStages.ProjectID = Projects.ProjectID
				inner join wfProcessInstances on CustomerLoans.LoanID = wfProcessInstances.EntityID
				inner join (select ProcessInstanceID,max(creationdate) as CreationDate
							from wfstepInstances group by ProcessInstanceID) as wfstepInstancesTable 
						on wfprocessInstances.ProcessInstanceID = wfstepInstancesTable.ProcessInstanceID
				INNER JOIN wfstepInstances ON (wfstepInstances.ProcessInstanceID = wfstepInstancesTable.ProcessInstanceID
							AND wfstepInstances.CreationDate = wfstepInstancesTable.CreationDate)
				inner join wfSteps on wfstepInstances.StepID = wfSteps.StepID
group by wfSteps.DisplayName,CustomerLoans.CommitteeID,Projects.ProjectID





GO


